//
//  DataAccess.m
//  Pitch
//
//  Created by zhujinhui on 14-9-7.
//  Copyright (c) 2014年 zhujinhui. All rights reserved.
//

#import "DataAccess.h"

@interface DataAccess (){
    sqlite3 *db;
}

@end

@implementation DataAccess

-(id)init{
    if (self = [super init]) {
        [self createDatabase];
    }
    return self;
}


-(void)createDatabase{
    NSString *dbp = [self databasePath];
    //if there is no database at path "dbp",then a database with the same name "dbp" will be created
    int isSuccess = sqlite3_open([dbp UTF8String], &db);
    /* Database filename (UTF-8) */
    if (isSuccess == SQLITE_OK) {
        NSLog(@"sql lite is ok");
    }else{
        NSLog(@"sqlite failed");
        sqlite3_close(db);
    }
}

-(void)execSql:(NSString *)sql
{
    char *err;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        sqlite3_close(db);
        NSLog(@"fail to execute sql!");
    }
}


-(NSArray *)selectTable:(NSString *)table atRow:(const int)row{
    NSString *sqlQuery = [NSString stringWithFormat:@"SELECT * FROM %@",table];
    sqlite3_stmt * statement;
    NSMutableArray *temAry = [[NSMutableArray alloc]init];
    
    int isPrepared = sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil);
    
    if (isPrepared == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char*)sqlite3_column_text(statement, row);
            NSString *nameStr = [[NSString alloc]initWithUTF8String:name];
            
            [temAry addObject:nameStr];
            
            //            int age = sqlite3_column_int(statement, 2);
            
        }
    }else{
        sqlite3_close(db);
        NSLog(@"not prepared,and error code:%i",isPrepared);
    }
    
    return temAry;
}

-(NSString *) selectOneFromTable:(NSString *)table withDictionary:(NSDictionary *)dic{
    NSString *resultStr = nil;
    NSArray *allKeys = [dic allKeys];
    NSMutableString *sqlQuery = [[NSMutableString alloc]initWithFormat:@"SELECT * FROM %@ where ",table];
    for (NSObject *keyItem in allKeys) {
        [sqlQuery appendFormat:@"%@=\'%@\' ",keyItem,[dic objectForKey:keyItem]];
    }
    
    sqlite3_stmt * statement;
    int isPrepared = sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil);
    
    if (isPrepared == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char*)sqlite3_column_text(statement, 1);
            resultStr = [[NSString alloc]initWithUTF8String:name];
        }
    }else{
        sqlite3_close(db);
        NSLog(@"not prepared,and error code:%i",isPrepared);
    }

    return resultStr;
}


-(NSString *) selectOneCityFromTable:(NSString *)table withDictionary:(NSDictionary *)dic{
    NSString *resultStr = nil;
    NSArray *allKeys = [dic allKeys];
    NSMutableString *sqlQuery = [[NSMutableString alloc]initWithFormat:@"SELECT * FROM %@ where ",table];
    for (NSObject *keyItem in allKeys) {
        [sqlQuery appendFormat:@"%@=\'%@\' ",keyItem,[dic objectForKey:keyItem]];
    }
    
    sqlite3_stmt * statement;
    int isPrepared = sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil);
    
    if (isPrepared == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char*)sqlite3_column_text(statement, 2);
            resultStr = [[NSString alloc]initWithUTF8String:name];
        }
    }else{
        sqlite3_close(db);
        NSLog(@"not prepared,and error code:%i",isPrepared);
    }
    
    return resultStr;
}


-(NSArray *) selectAllFromTable:(NSString *)table withDictionary:(NSDictionary *)dic{
    NSArray *allKeys = [dic allKeys];
    NSMutableString *sqlQuery = [[NSMutableString alloc]initWithFormat:@"SELECT * FROM %@ where ",table];
    for (NSObject *keyItem in allKeys) {
        [sqlQuery appendFormat:@"%@=\'%@\' ",keyItem,[dic objectForKey:keyItem]];
    }
    
    NSMutableArray *temAry = [[NSMutableArray alloc]init];
    
    sqlite3_stmt * statement;
    int isPrepared = sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil);
    
    if (isPrepared == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char*)sqlite3_column_text(statement, 0);
            NSString *tempStr = [[NSString alloc]initWithUTF8String:name];
            NSLog(@"name:%@",tempStr);
            [temAry addObject:tempStr];
        }
    }else{
        sqlite3_close(db);
        NSLog(@"not prepared,and error code:%i",isPrepared);
    }
    
    return temAry;
}

-(NSArray *) selectAllZonesFromTable:(NSString *)table withDictionary:(NSDictionary *)dic{
    NSArray *allKeys = [dic allKeys];
    NSMutableString *sqlQuery = [[NSMutableString alloc]initWithFormat:@"SELECT * FROM %@ where ",table];
    for (NSObject *keyItem in allKeys) {
        [sqlQuery appendFormat:@"%@=\'%@\' ",keyItem,[dic objectForKey:keyItem]];
    }
    
    NSMutableArray *temAry = [[NSMutableArray alloc]init];
    
    sqlite3_stmt * statement;
    int isPrepared = sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil);
    
    if (isPrepared == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char*)sqlite3_column_text(statement, 1);
            NSString *tempStr = [[NSString alloc]initWithUTF8String:name];
            NSLog(@"name:%@",tempStr);
            [temAry addObject:tempStr];
        }
    }else{
        sqlite3_close(db);
        NSLog(@"not prepared,and error code:%i",isPrepared);
    }
    
    return temAry;
}



-(NSString *)databasePath{
    return nil;
}

-(void)closeDatabase{
    sqlite3_close(db);
}


//-(NSString *)databaseName{
//    return nil;
//}



@end
